<?php
/*
# ------------------------------------------------------------------------
# JA Job Board Package
# ------------------------------------------------------------------------
# Copyright (C) 2004-2010 JoomlArt.com. All Rights Reserved.
# @license GNU/GPLv3 http://www.gnu.org/licenses/gpl-3.0.html
# Author: JoomlArt.com
# Websites: http://www.joomlart.com - http://www.joomlancers.com.
# ------------------------------------------------------------------------
*/

jimport( 'joomla.application.component.model' );

/**
 * @package Joomla
 * @subpackage JobBoard
 */
class JAJobBoardModelJAManageFields extends JModel
{
    var $_data;
    var $_table;
    var $_pagination;
    /**
    * Return the query is used to retrive all row from database
    * @return string The query is used 
    */
    function getItems($where_more=''){
    	global $mainframe, $option;
    	
    	$db = &JFactory::getDBO();
    	
    	$limit				= $mainframe->getUserStateFromRequest( 'global.list.limit',							'limit',			$mainframe->getCfg( 'list_limit' ),	'int' );
		$limitstart			= $mainframe->getUserStateFromRequest( $option.'.jamanagefields.limitstart',		'limitstart',		0,				'int' );
    	$filter_table		= $mainframe->getUserStateFromRequest( $option .".jamanagefields.filter_table",		'filter_table',		'',	'word' );
		$filter_form		= $mainframe->getUserStateFromRequest( $option.".jamanagefields.filter_form",		'filter_form',		'',	'string' );    	
		$filter_order		= $mainframe->getUserStateFromRequest( $option .".jamanagefields.filter_order",		'filter_order',		'f.field_ordering',	'cmd' );
		$filter_order_Dir	= $mainframe->getUserStateFromRequest( $option .".jamanagefields.filter_order_Dir",	'filter_order_Dir',	'ASC',			'word' );
		$search				= $mainframe->getUserStateFromRequest( $option .".jamanagefields.search",			'search',			'',				'string' );
		$search				= JString::strtolower( $search );
	
        // just in case filter_order get's messed up
//		$filter_order = 'f.field_ordering';
		if ($filter_order) {
			$orderby = ' ORDER BY '.$filter_order .' '. $filter_order_Dir .', f.field_state DESC';
		} else {
			$orderby = ' ORDER BY f.field_ordering, f.field_state DESC ';
		}
		
		// $where[] = "f.field_state != 'del' ";
		$where = array();
		if($search){
		    $where[] = " (f.field_name LIKE ". $db->Quote( '%'.$db->getEscaped( $search, true ).'%', false ).")"
		    	." OR (f.label LIKE ". $db->Quote( '%'.$db->getEscaped( $search, true ).'%', false ).")";
		}
		
		if($filter_table){
			$where[] = " f.table_name = ". $db->Quote( $filter_table, false );
		}
		
		if($filter_form && $where_more==''){
			$where[] = " f.form_id = ". $db->Quote( $filter_form, false );
		}
		
		$where_clause = " WHERE 1=1 ";
		if(count($where)){
		    $where_clause .=  ' and '.implode(' and ', $where);
		}

        $query = "select * from #__ja_form_fields as f"
            	. $where_clause
            	. $where_more
            	. $orderby
        	;
		
        $db->setQuery($query);
		
		$items = $db->loadObjectList();
		
		$total = count( $items );
		
		jimport('joomla.html.pagination');
		$this->_pagination = new JPagination( $total, $limitstart, $limit );
		
		$list = array_slice( $items, $this->_pagination->limitstart, $this->_pagination->limit );
		
		return $list;
    }
        
    /**
    * Get field table instance
    * @return JTable Configuration table object
    */
    function &_getTable(){
        if($this->_table == null){
        	$this->_table = &JTable::getInstance('JA_Managefields', 'JobBoardTable');
		}
		return $this->_table;
	}
	
	
	/**
	* Remove field record
	* @return Number of removed records
	*/
	function remove($task = 'remove'){
	    $cid = JRequest::getVar('cid',array(), 'post', 'array');
	    $n =  count($cid);
	    JArrayHelper::toInteger( $cid );
	    
	    if($n){
	    	$db = &JFactory::getDBO();
	    	$query = "SELECT * FROM #__ja_form_fields WHERE id = ". implode(" OR id = ", $cid) .' and is_system=0';
	    	$db->setQuery($query);
	    	$result = $db->loadObjectList();
	    	if (is_array($result))
	    		if (count($result)<$n)
	    		{
	    			JError::raiseWarning(1, JText::_("You can not delete system field"));
	    			return 0;
	    		}
	        if($task == 'drop')
				$query = "UPDATE #__ja_form_fields SET field_state = 'drp', field_ordering = -1 WHERE id = ". implode(" OR id = ", $cid) .' and is_system=0';
			else if($task == 'remove')
				$query = "DELETE FROM #__ja_form_fields WHERE id = ". implode(" OR id = ", $cid) .' and is_system=0';
	        
			
	        $db->setQuery($query);
	        $result = $db->query();
	        if($result){
	            $table = $this->_getTable();
	            $table->reorder();
	            return $n;
			}
			else{
				JError::raiseWarning(1, JText::_("You can not delete system field"));
				return 0;
			}
		}
		
		return 0;
	}
	
	
	
	function orderItem($item, $movement){
		$row = &$this->_getTable();
	    $row->load($item);
		if(!$row->move($movement)){
			$this->setError($row->getError());
			return false;
		}
		return true;
	}
	
	function setOrder($items){
		$total		= count( $items );
		$row		=& $this->_getTable();

		$order		= JRequest::getVar( 'field_ordering', array(), 'post', 'array' );
		JArrayHelper::toInteger($order);

		// update ordering values
		for( $i=0; $i < $total; $i++ ) {
			$row->load( $items[$i] );
			// track parents
			if ($row->field_ordering != $order[$i]) {
				$row->field_ordering = $order[$i];
				if (!$row->store()) {
					$this->setError($row->getError());
					return false;
				}
			} // if
		} // for
		
		return true;
	}
	
	/**
	* Get configuration item
	* @return JobboardTableJA_ManageFields Table object
	*/
	function getItem(){
		static $item;
		if (isset($item)) {
			return $item;
		}

		$table =& $this->_getTable();

		// Load the current item if it has been defined
		$edit	= JRequest::getVar('edit',true);
		$cid = JRequest::getVar( 'cid', array(0), '', 'array' );
		JArrayHelper::toInteger($cid, array(0));
		
		if ($edit) {
			$table->load($cid[0]);
		}

	    $item = $table;
	    return $item;	    
	}
	
		
	
	/**
	* Checkout item
	* 
	*/
	function checkout($uid = null){
		$id = JRequest::getVar('cid', array(0), '', 'array');
		JArrayHelper::toInteger( $id, array(0) );

		// Make sure we have a user id to checkout the article with
		if (is_null($uid)) {
			$user	=& JFactory::getUser();
			$uid	= $user->get('id');
		}

		// Lets get to it and checkout the thing...
		$item	= $this->getItem();
		if(!$item->checkout($uid, $id[0])) {
			$this->setError($this->_db->getErrorMsg());
			return false;
		}

		return true;
	}
	
	/** 
	* Store configuration item
	* @param array The post array
	*/
	function store(){
	    // Initialize variables
		$db		=& JFactory::getDBO();
		$row	=& $this->getItem();
		$post	= $this->setState('request');
		
		if (!$row->bind( $post )) {
			echo "<script> alert('".$row->getError(true)."'); window.history.go(-1); </script>\n";
			return false;
		}
		
		if (!$row->check())
		{
			echo "<script> alert('".$row->getError(true)."'); window.history.go(-1); </script>\n";
			return false;
		}
       
		if (!$row->store())
		{
			echo "<script> alert('".$row->getError(true)."'); window.history.go(-1); </script>\n";
			return false;
		}
		
		$row->reorder();
		$row->checkin($row->id);
		
		return $row->id;
	}
	
	/**
	* Get section list
	* @return array The section list
	*/
	function getFieldSectionList(){
		$sections = array(
				"" => JText::_("Select Section"),
				"1" => "First Section",
				"2" => "Second Section"
			);
		return $sections;
	}

	/**
	* Get the list of field in its order
	* @param $table Get field list of the table. All fields will be selected if this parameter is null or empty
	* @return array 
	*/
	function getFieldList($table = null){
		$db = &JFactory::getDBO();
		
		$query = "SELECT * FROM #__ja_form_fields " . ($table?"WHERE table_name = '$table'" : "") . " ORDER BY field_ordering";
		$db->setQuery($query);
		$fields = $db->loadObjectList();
		
		$result = array();
		for($i = 0; $i < count($fields); $i++){
		    $field = $fields[$i];                                                                                                                    
		    $result[$field->field_ordering] = $field->label;
		}
		
		return $result;
	}
	
	/**
	* Get update information for a table
	* 
	*/
	function getUpdateTableSummary($table){
		$db = &JFactory::getDBO();
	}
	
	/**
	 * Alter table by sql commands
	 */
	function updateTable($table){
		$db = &JFactory::getDBO();

		$tableFields = $db->getTableFields("#__".$table);
		$tableFields = $tableFields["#__".$table];
		
		if(empty($table) )
			return false;
		// Get fields of the table
		$cid = JRequest::getVar( 'cid', array(0), '', 'array' );
		JArrayHelper::toInteger($cid, array(0));
		$query = "SELECT * FROM #__ja_form_fields WHERE id = '$cid[0]'";// AND field_state != 'upd' ORDER BY field_ordering";
		$db->setQuery($query);
		$fields = $db->loadObjectList();

		$queries = "";
		for($i = 0,$n = count($fields); $i < $n; $i++){
			$field = $fields[$i];
			$queries .= $this->makeSqlCommand($field, $tableFields);
			
		}
		$db->setQuery($queries);
			
		return $db->queryBatch();
	}
	
	/**
	 * Generate sql command to update table
	 * @return string command to alter the table
	 */
	function makeSqlCommand($field, $tableFields = array()){
				
		$query = "";
		switch($field->field_state){
			case 'drp':
				if($field->field_name && array_key_exists( $field->field_name, $tableFields)){
					$query = "ALTER TABLE #__". $field->table_name ." DROP ". $field->field_name.";";
				}
                $query .= "DELETE FROM #__ja_form_fields WHERE id=". $field->id.";";
				$query .= "DELETE FROM #__ja_form_lists WHERE field_id=". $field->id.";";
				break;
			case 'mod':
				if ($field->old_table_name==$field->table_name)
				{
					if($field->old_field_name && $field->field_name && $field->field_type && array_key_exists( $field->old_field_name, $tableFields)){
				
					$query = "ALTER TABLE #__". $field->table_name ." CHANGE ". $field->old_field_name 
				    ." " . $field->field_name." ".JobBoardConstant::getMapDBType($field->field_type)
                    //." CHARACTER SET utf8 COLLATE utf8_general_ci "
                    ." " . ($field->is_required ? " NOT NULL " : "") . ($field->field_init != ''? " DEFAULT '". $field->field_init ."'" : "" )
                    .";";
					
					}
					$query .= "UPDATE #__ja_form_fields SET old_table_name=table_name, old_field_name = field_name, old_field_type = field_type, field_state = 'chg' WHERE id=". $field->id.";"; 					
				}
				else 
				{
					
					$query = "ALTER TABLE #__". $field->table_name ." ADD ". $field->field_name 
					." ".JobBoardConstant::getMapDBType($field->field_type)
                    ." " . ($field->is_required ? " NOT NULL " : "") . ($field->field_init != ''? " DEFAULT '". $field->field_init ."'" : "" )
                    .";";
                    $query .= "ALTER TABLE #__". $field->old_table_name ." DROP ". $field->old_field_name 
					.";";
					$query .= "UPDATE #__ja_form_fields SET old_table_name=table_name, old_field_name = field_name, old_field_type = field_type, field_state = 'chg' WHERE id=". $field->id.";"; 
					
				}
				
				
				break;
			case 'ins':
				if (array_key_exists( $field->field_name, $tableFields))
					$query = "ALTER TABLE #__". $field->table_name ." DROP ". $field->field_name.";";
					
				if($field->field_name && $field->field_type){
					$query .= "ALTER TABLE #__". $field->table_name ." ADD ". $field->field_name
						." ".JobBoardConstant::getMapDBType($field->field_type)
						//." CHARACTER SET utf8 COLLATE utf8_general_ci "
                        ." " . ($field->is_required ? " NOT NULL " : "") . ($field->field_init != ''? " DEFAULT '". $field->field_init ."'" : "" )
                        .";";
                    $query .= "UPDATE #__ja_form_fields SET field_state = 'add' WHERE id=". $field->id.";";
//					echo $query;exit;
				}
				break;
			default:
				break;
		}
		
		return $query;
	}		
	
	function dopublish($name, $value){
		$db		=& JFactory::getDBO();
		
		$ids = JRequest::getVar('cid', array());		
		$ids = implode( ',', $ids );
		
		$query = "UPDATE #__ja_form_fields"
		. "\n SET $name = " . intval( $value )
		. "\n WHERE id IN ( $ids ) "
		;
		
		$db->setQuery( $query );
		if (!$db->query()) {
			return false;
		}
		return true;
	}

	function doreverse(){		
		$item = $this->getItem();
		if(!$item->id){
			JError::raiseWarning(1, JText::_("The item not found."));
			return false;
		}
		if ($item->field_state=='mod')
		{
			$item->field_name = $item->old_field_name;
			$item->field_type = $item->old_field_type;
			$item->table_name = $item->old_table_name;
			$item->field_state = 'chg';
			
			if(!$item->store()){
				JError::raiseWarning(1, JText::_('An error occurred while storing data'));
				return false;
			}
		}
		elseif ($item->field_state=='ins') 
		{
			if (!$item->delete())
			{
				JError::raiseWarning(1, JText::_('An error occurred while storing data'));
				return false;
			}
		}
		return true;
	}
}
?>